from PyQt5 import QtWidgets, QtSql, QtGui, QtCore
import os
import config_json
import openpyxl
import traceback
import getpath
import bjItems
import re
import logging
import datetime
import myglobal
import d4460
# from cali import Cali
import time
import dspec
import dspec_point
import copy
from mydb import MyDB_Base
class MyDb(MyDB_Base):
    def __init__(self,mainw):
        super().__init__(mainw)
    def getDataDays(self, v):
        filename=config_json.setting['database']
        objs=[]
        if config_json.setting["sblx"]=="spec":
            objs=dspec.getDataObjects(filename)
        elif config_json.setting["sblx"]=="spec_point":
            objs=dspec_point.getDataObjects(filename)
        elif config_json.setting["sblx"]=="4460":
            objs=d4460.getDataObjects(filename)
        if len(objs)>0:
            self.resetProperties(objs[0].items)
        return objs
    def resetProperties(self,items):
        self.properties = [
            {"ele":None,"delegate":None,'width':130,'attr': "sampleid",'visible':True,  'header': "id"},
            {"ele":None,"delegate":self.editDelegate,'width':80,'attr': "beizhu",'visible':True,  'header': "备注"},
        ]
        v=list(items.keys())
        # try:
        #     v.sort()
        # except TypeError:
        #     pass 
        # logging.info(v)
        for item in v:
            one={"ele":item,"delegate":self.editDelegate,'width':90,'attr': item[0]+"_"+item[1],'visible':True,  'header': item[0]+" "+item[1]}
            self.properties.append(one)        
    def calc(self):
        if myglobal.cali==None:
            myglobal.cali = Cali()
            myglobal.cali.show()
        else:
            pass
    def cali_close(self):
        self.cali=None
    def calc_one(self,record):
        sample = {"items": [], "conc": []}
        sample["sampleid"]=record.value(record.indexOf("sampleid"))
        sample["oldid"]=record.value(record.indexOf("id"))
        for i in range(len(self.fields)):
            f = self.fields[i]
            ele=f.get("ele")
            if ele==None:
                pass
            else:
                sample["items"].append(("ele",""))
                conc0 = myfloat(record.value(record.indexOf(ele+"_")))
                conc=self.calc_ele(ele,conc0)
                sample["conc"].append(conc)
                print(ele,conc)
        self.iTable(sample,"Result",1)#calced=1

    def calc_ele(self,ele,conc0):
        xishu=myglobal.db.getXishuCurrent(myglobal.methodid,ele)
        logging.info([myglobal.methodid,ele,xishu])
        if xishu==None:
            return None
        else:
            [x2,x1,x0,r,cid,must00,ci]=xishu
            return x2*conc0*conc0+x1*conc0+x0
    def calc_ele_curveid(self,ele,curveid,conc0):
        xishu=myglobal.db.getXishu(myglobal.methodid,ele,curveid)
        logging.info([myglobal.methodid,ele,curveid,xishu])
        if xishu==None:
            return None
        else:
            [x2,x1,x0,r,cid,must00,ci]=xishu
            logging.info([x2,x1,x0,conc0])
            conc0=myglobal.myfloat(conc0)
            return x2*conc0*conc0+x1*conc0+x0            

    def inSamples(self, samples, one):
        at = 0
        for s in samples:
            if s["sampleid"] == one:
                return at
            at += 1
        return -1

    def inItems(self, items, one):
        for s in items:
            if s[0] == one[0] and s[1] == one[1]:
                return True
        return False

    def newSample0(self, samples, sampleid, ele, wave, conc, mass, volume):
        # sampleid can not be duplicate,so must with _1,_2
        at = self.inSamples(samples, sampleid)
        if at != -1:
            if self.inItems(samples[at]["items"], (ele, wave)):
                pass
            else:
                samples[at]["conc"].append(conc)
                samples[at]["items"].append((ele, wave))
            return False
        else:
            samples.append({"mass": mass, "volume": volume, "sampleid": sampleid, "conc": [
                conc], "items": [(ele, wave)]})
            return True

    def newSample(self, samples, sampleid, ele, wave, conc, mass, volume):
        # can not same sampleid followed
        if len(samples) >= 1:
            sample = samples[-1]
            if sampleid == sample["sampleid"]:  # same sampleid with last
                at2 = self.inItems(sample["items"], (ele, wave))
                if at2:  # same element wave donot capture
                    pass
                else:
                    sample["conc"].append(conc)
                    sample["items"].append((ele, wave))
                return False
            else:
                samples.append({"mass": mass, "volume": volume, "sampleid": sampleid, "conc": [
                    conc], "items": [(ele, wave)]})
                return True
        else:  # []
            samples.append({"mass": mass, "volume": volume, "sampleid": sampleid, "conc": [
                conc], "items": [(ele, wave)]})
            return True

    # def elementWave(self, s):
    #     if s==None:
    #         return("","")
    #     wave = re.search(r"\d+(\.\d+)?", s)
    #     if wave!=None:
    #         wave=wave.group()
    #         element = s.split(" ")[0]
    #     else:
    #         wave=""
    #         element=s
    #     return (element, wave)
    # def import_4460(self, fname):
    #     # 溶液标签    "P (213.618nm)"    "P (214.914nm)"    "Pb (217.000nm)"    "Pb (220.353nm)"    "Ta (226.142nm)"    "Ta (226.230nm)"    "Ti (334.188nm)"    "Ti (334.941nm)"
    #     # BLK 0.000026 (%)    -0.000214 (%)   0.000659 (%)    0.000601 (%)    -0.000040 (%)   -0.000028 (%)   -0.000051 (%)   -0.000001 (%)
    #     logging.info("import_4460=======================")
    #     # file=open(
    #     #     fname, encoding=config_json.setting[config_json.setting["sblx"]]["encoding"])
    #     # csv_reader = csv.reader(file)
    #     # data = []
    #     # for row in csv_reader:
    #     #     data.append(row)
    #     # file.close()
    #     data=d4460.getdata(fname)
    #     create_table = False
    #     samples = []
    #     at = 1
    #     while True:
    #         sample = {"items": [], "conc": []}
    #         try:
    #             sample["mass"] = "0"
    #             sample["volume"] = "0"
    #             sample["items"] = data[0][1:]
    #             sampleid = data[at][0]
    #             sample["sampleid"] = data[at][0]
    #             sample["conc"] = []
    #             for one in data[at][1:]:
    #                 sample["conc"].append(one)  # remove %
    #             for i in range(len(sample["items"])):
    #                 (ele, wave) = self.elementWave(sample["items"][i])
    #                 sample["items"][i] = (ele, wave)
    #             logging.info(sampleid)
    #             self.addCalcColumn(sample)
    #             # for i in range(len(sample["conc"])):#one in sample["conc"]:
    #             #     ele=sample["items"][i][0]
    #             #     print(ele)
    #             #     new=self.calc_ele(ele,sample["conc"][i])
    #             #     if new!=None:
    #             #         sample["conc"][i]=("%.4f" % sample["conc"][i])+";"+("%.4f" % new)
    #         except IndexError:
    #             break
    #         if not create_table:
    #             self.cTable(sample)
    #             create_table = True
    #         self.iTable(sample)
    #         if sampleid == "":
    #             break
    #         samples.append(sample)
    #         at += 1
    #     logging.info(samples)
    #     logging.info(len(samples))
    def addCalcColumn(self,sample):
        logging.info("===================================")
        logging.info(sample)
        items_add=[]
        concs_add=[]
        for i in range(len(sample["items"])):#one in sample["conc"]:
            ele=sample["items"][i][0]
            ids=myglobal.db.getAllCurveid(myglobal.methodid,ele)
            if len(ids)==0:
                pass
            else:
                conc=sample["conc"][i]
                conc=myglobal.myfloat(conc)
                sample["items"][i]=(sample["items"][i][0],str(ids[0]))
                new=self.calc_ele_curveid(ele,ids[0],conc)
                newstr=("%.4f" % conc)+";"+("%.4f" % new)
                sample["conc"][i]=newstr
                for curveid in ids[1:]:
                    items_add.append((ele,str(curveid)))
                    new=self.calc_ele_curveid(ele,curveid,conc)
                    newstr=("%.4f" % conc)+";"+("%.4f" % new)
                    concs_add.append(newstr)
        for i in range(len(items_add)):
            for j in range(len(sample["items"])):
                ele=sample["items"][j][0]
                if items_add[i][0]==ele:
                    sample["items"].insert(j+1,items_add[i])
                    sample["conc"].insert(j+1,concs_add[i])
                    break

        # return sample1
    # def import_spec(self, fname):
    #     xlBook = load_workbook(filename=fname)
    #     table = xlBook.worksheets[0]
    #     logging.info(dir(table))
    #     create_table = False
    #     data = []
    #     for row in table.values:
    #         r = []
    #         for one in row:
    #             if one is not None:
    #                 r.append(one)
    #             else:
    #                 r.append(None)
    #         data.append(r)
    #     samples = []
    #     at = 0
    #     while True:
    #         sample = {"items": [], "conc": []}
    #         try:
    #             sampleid = data[at + 1][0]
    #             sample["sampleid"] = sampleid

    #             logging.info(sampleid)
    #         except IndexError:
    #             break
    #         col = 1
    #         while True:
    #             try:
    #                 # logging.info(at+2,col)
    #                 item = data[at + 2][col]
    #                 conc = data[at + 3][col]
    #                 # should replace blank to other char
    #                 sample["items"].append((item, ""))  # .append(item + "_")
    #                 conc=myglobal.myfloat(conc)
    #                 sample["conc"].append(conc)
    #                 sample["mass"] = ""
    #                 sample["volume"] = ""
    #                 self.addCalcColumn(sample)
    #             except IndexError:
    #                 break
    #             if (item == ""):
    #                 break
    #             col += 1
    #         if not create_table:
    #             self.cTable(sample)
    #             create_table = True
    #         self.iTable(sample)
    #         if sampleid == "":
    #             break
    #         samples.append(sample)
    #         at += 7
    #     logging.info(samples)
    #     logging.info(len(samples))


    def lims(self):
        if self.checkBoth():
            return
        n = len(self.mainw.objects2)
        if n == 0:
            return
        ffbh = self.mainw.ui.cmb_ffbh.text()
        syy = self.mainw.ui.cmb_syy.text()
        ysry = self.mainw.ui.cmb_ysry.text()
        syyqm = self.mainw.ui.cmb_syyqm.text()
        yqbh = config_json.setting["yqbh"]
        queren = ""
        # if config_json.setting["checkSingle"]==1:
        #     single=self.checkSingle()
        #     if single!="":
        #         queren = queren+"样品(%s)没有平行样!!!\n" % single 
        #         queren = queren+"\n"
        queren = queren+"方法编号(%s)\n" % ffbh
        queren = queren+"实验员(%s)\n" % syy
        queren = queren+"一审(%s)\n" % ysry
        queren = queren+"实验员签名(%s)\n" % syyqm
        queren = queren+"仪器编号(%s)\n" % yqbh
        logging.info(["ejff",config_json.setting["ejff"]])
        ejff= self.mainw.ui.cmb_ff2.text()
        ejfxjs= self.mainw.ui.cmb_js2.text()
        if config_json.setting["ejff"]==1 and ejff!="" and ejfxjs!="":
            queren = queren+"二级方法(%s)\n" % ejff
            queren = queren+"二级分析技术(%s)" % ejfxjs
        r = QtWidgets.QMessageBox.question(
            self.mainw, '输出', queren, QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No, QtWidgets.QMessageBox.Yes)
        if r == QtWidgets.QMessageBox.No:
            return
        yqbh=config_json.setting["yqbh"]
        if len(yqbh)>4:
            yqbh=yqbh[-4:]
        if self.mainw.ui.lineEdit_filename.text() == "":
            file_name = yqbh + "_" + timestr()
        else:
            file_name = yqbh+"_"+self.mainw.ui.lineEdit_filename.text()
        path = config_json.setting["output_folder"]
        filepath = os.path.join(path, file_name)
        self.output_xlsx(filepath)

    def output_xlsx(self, filepath):
        n = len(self.mainw.objects2)
        if n == 0:
            return
        filepath = filepath + ".xlsx"
        # 1. 创建文件对象（指定文件名，模式，编码方式）a模式 为 下次写入在这次的下一行
        # wb = openpyxl.Workbook()
        fname=config_json.setting["database"]
        wb = openpyxl.load_workbook(filename=fname)
        # ws = wb.active
        ws = wb.create_sheet("caiji")
        wb.move_sheet("caiji", -10) 
        # 3. 构建列表头
        row = ["id", ]
        for f in self.properties:
            if f["visible"] and f.get("ele") != None:
                row.append(f["header"].split(" ")[0])
        row.append("备注")
        ws.append(row)
        for row_idx in range(n):
            record = self.mainw.objects2[row_idx]
            sampleid = str(record.sampleid)
            if config_json.setting["pingxing"]==1:
                sampleid=self.remove_pingxing(sampleid)
            row = list([sampleid])
            # olddata=self.getConc0(record.value(record.indexOf("oldid")))
            for f in self.properties:
                if f["visible"] and f.get("ele") != None:
                    conc =getattr(record,f["attr"])# record.value(record.indexOf(f["name"]))
                    # logging.info([conc,type(conc)])
                    row.append(conc+";"+conc)
            row.append(getattr(record,"beizhu"))
            ws.append(row)
        ffbh = self.mainw.ui.cmb_ffbh.text()
        syy = self.mainw.ui.cmb_syy.text()
        ysry = self.mainw.ui.cmb_ysry.text()
        yqbh = config_json.setting["yqbh"]
        # tpbh = self.ui.lineEdit_tpbh.text()
        # nb = self.ui.lineEdit_nb.text()
        # bc= self.ui.lineEdit_bc.text()
        # nd= self.ui.lineEdit_nd.text()
        # config_json.setting["nb"]=nb
        # config_json.setting["nd"]=nd
        # config_json.setting["bc"]=bc
        # config_json.setting["tpbh"]=tpbh
        syyqm = self.mainw.ui.cmb_syyqm.text()
        row = ["ffbh&" + ffbh, "", "", "", ""]
        ws.append(row)
        row = ["syy&" + syy, "", "", "", ""]
        ws.append(row)
        row = ["ysry&" + ysry, "", "", "", ""]
        ws.append(row)
        row = ["yqbh&" + yqbh, "", "", "", ""]
        ws.append(row)
        row = ["syyqm&" + syyqm, "", "", "", ""]
        ws.append(row)
        # row = ["tpbh&" + tpbh, "", "", "", ""]
        # ws.append(row)
        # row = ["nbys&" + nb, "", "", "", ""]
        # ws.append(row)
        # row = ["bc&" + bc, "", "", "", ""]
        # ws.append(row)
        # row = ["nd&" + nd, "", "", "", ""]
        # ws.append(row)
        ff2= self.mainw.ui.cmb_ff2.text().strip()
        js2= self.mainw.ui.cmb_js2.text().strip()
        if config_json.setting["ejff"]==1 and ff2 != "" and js2 != "":
            ws.append(["ejff&" + ff2,"", "", "", ""])  # todo
            ws.append(
                ["ejfxjs&" + js2, "", "", "", ""])
        ws.column_dimensions['A'].width = 20.0
        i=0
        for col in ws.columns:
            if i>0:
                ws.column_dimensions[col[0].column_letter].width = 15.0
            i+=1        # other stuff
        # self.output_ws2(wb) #beifen
        # openpyxl.utils.cell.get_column_letter
        # ws = wb.create_sheet("origin")
        # hang=self.output_origin(ws)+1
        # if config_json.setting["sblx"] =="ajl_pdf":
        #     ajlpdf.output_curve(ws,hang)
        # elif config_json.setting["sblx"] =="icap":
        #     icap.output_curve(ws,hang)
        # if config_json.setting["sblx"] =="spec":
        # spec1=spec.Spec()
        # spec1.spec_output_curve(self.fields,ws,hang)
        # .spec_output_curve(ws,hang)
        wb.save(filepath)
        self.mainw.ui.textEdit.insertPlainText(filepath + "\n")
        scrollbar = self.mainw.ui.textEdit.verticalScrollBar()
        scrollbar.setSliderPosition(scrollbar.maximum())
    def spec_output_curve(self,ws,hang):
        ts=[]
        eles=[]
        for f in self.fields:
            if f["visible"] and f.get("ele") != None:
                eles.append(f.get("ele"))
        ts=self.getImages(eles)
        self.outputImages(ws,hang,ts)
    def getImages(self,eles):
        r=[]
        for ele in eles:
            curve=self.getImage_ele(ele)
            if curve!=None:
                r.append(curve)
        return r
    def getImage_ele(self,ele):
        xishu=myglobal.db.getXishuCurrent(myglobal.methodid,ele)
        data=myglobal.db.getCurveData(myglobal.methodid,ele)
        if xishu!=None:
            [x2,x1,x0,r,curveid,must00,ci]=xishu
            params=[]
            params.append("ele="+ele)
            params.append("x2="+str(x2))
            params.append("x1="+str(x1))
            params.append("x0="+str(x0))
            params.append("r="+str(r))
            filename=os.path.join(getpath.getpath(),"images",str(myglobal.methodid)+"_"+ele+"_"+str(curveid)+".jpg")
            return (filename,params,data,ele)
        else:
            return None
    def outputImages(self,ws,hang,ts):#outputsheet,pdf filename,0-n page
        ss=QtWidgets.qApp.screens()
        if len(ss)<1:
            rowh=13.5/72.0*96
        else:
            rowh=13.5/72.0*ss[0].physicalDotsPerInch() #bang to pixel
        at_row=hang
        for i in range(len(ts)):
            (filename,paras,data,ele)=ts[i]
            ws["A"+str(at_row)]="sampleid"
            ws["B"+str(at_row)]=ele+"-STD"
            ws["C"+str(at_row)]=ele
            ws["D"+str(at_row)]=ele+"'"
            j=1
            for one in data:
                ws["A"+str(at_row+j)]=one[0]
                ws["B"+str(at_row+j)]=one[1]
                ws["C"+str(at_row+j)]=one[2]
                ws["D"+str(at_row+j)]=one[3]
                j+=1
            if os.path.exists(filename):
                img = Image(filename)
                img.width=300#not too big
                img.height=200
                n=int((img.height)/rowh)+2
                ws.add_image(img, "F"+str(at_row))  # 图片 插入 A1 的位置上
            else:
                n=0
            # at_row=i*n+hang
            for j in range(len(paras)):
                ws["E"+str(at_row+j)]=paras[j]
            ws.column_dimensions['E'].width = 15.0
            if len(data)>n:
                at_row=at_row+len(data)
            else:
                at_row=at_row+n
    def output_origin(self, ws):
        n = self.model.rowCount()
        row = ["id"]
        for f in self.fields:
            if f.get("ele")!=None:
                if f.get("xishu")!=None:
                    row.append(f["ele"]+" "+f["wave"]+ "\n("+f["title"].split(" ")[0]+" "+str(f["xishu"])+")")
                else:
                    row.append(f["title"])
        ws.append(row)
        hang=1
        for row_idx in range(n):
            record = self.model.record(row_idx)
            row = list([record.value(record.indexOf("Sampleid"))])
            for f in self.fields:
                if f.get("ele")!=None:
                    conc = record.value(record.indexOf(f["name"]))
                    row.append(conc)
            ws.append(row)
            hang+=1
        return hang

if __name__ == "__main__":
    pass